import pymysql
from django.conf import settings

dbSettings = settings.DATABASES["default"]

host = dbSettings.get("HOST")
user = dbSettings.get("USER")
passwd = dbSettings.get("PASSWORD")
db = dbSettings.get("NAME")
port = dbSettings.get("PORT")


class SqlHelper(object):
    def __init__(self):
        # 读取配置文件
        self.connect()

    def connect(self):
        self.conn = pymysql.connect(host=host, port=port, user=user, passwd=passwd, db=db, charset='utf8')
        self.cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)

    def get_list(self, sql, args=None, type=None):
        self.cursor.execute(sql, args)
        result = self.cursor.fetchall()

        if result and type is not None:
            arr = []
            for sqlDict in result:
                arr.append(self.__toObject(sqlDict, type))
            return arr;
        return result

    def get_one(self, sql, args=None, type=None):
        self.cursor.execute(sql, args)
        result = self.cursor.fetchone()

        if result and type is not None:
            return self.__toObject(result, type)
        return result

    def __toObject(self, sqlDict, type=None):
        if sqlDict and type is not None:
            t = type()
            for key in sqlDict:
                if hasattr(t, key):
                    setattr(t, key, sqlDict[key])
            return t
        else:
            return None

    def get_many(self, sql, args=None):
        self.cursor.fetchmany(sql, args)
        self.conn.commit()

    def insert_many(self, sql, args=None):
        # self.cursor.executemany('insert into bd(id,name)values(%s,%s)',[(1,'alex'),(2,'eric')])
        effrows = self.cursor.executemany(sql, args)
        self.conn.commit()
        return effrows

    def insert(self, sql, args=None):
        effrows = self.cursor.execute(sql, args)
        self.conn.commit()
        return (self.cursor.lastrowid, effrows)

    def update(self, sql, args=None):
        effrows = self.cursor.execute(sql, args)
        self.conn.commit()
        return effrows;

    def callproc(self, procname, *args, **kwargs):
        '''
        :param procname:存储过程名称
        :param args:存储过程参数 注意out参数也需要传值 值类型随意
        :param kwargs:getParams 获取存储过程内部自定义@参数
                     isGetInnerSql是否得到存储过程内部select语句返回值
        :return:返回元组(存储过程定义输入/输出参数,用户自定义变量结果,存储过程内部select结果)
        '''
        if len(args) == 0:
            args = None;
        else:
            arr = ['@_%s_%s' % (procname, i,) for i in range(len(args))]
            inout = "SELECT " + ",".join(arr)

        retarr = [None, None, None]
        self.cursor.callproc(procname, args)

        if kwargs.get("isGetInnerSql"):
            result = self.cursor.fetchall()
            if result and kwargs.get("type"):
                arr = []
                for sqlDict in result:
                    arr.append(self.__toObject(sqlDict, type))
                retarr[2] = arr
            else:
                retarr[2] = result

        if inout:
            self.cursor.execute(inout)
            result = self.cursor.fetchone()
            retarr[0] = result

        if kwargs.get("getParams") is not None:
            params = "SELECT %s" % (",".join(kwargs.get("getParams")))
            self.cursor.execute(params)
            result = self.cursor.fetchone()
            retarr[1] = result

        return tuple(retarr)

    def close(self):
        self.cursor.close()
        self.conn.close()
